package com.liuxl.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.support.MetaDataAccessException;

import com.github.pagehelper.Page;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.liuxl.db.bo.Column;
import com.liuxl.db.bo.SQL;
import com.liuxl.db.bo.Table;
import com.liuxl.db.tool.DatabaseTool;
import com.liuxl.db.tool.DatabaseToolFactory;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;

/**
 * 统一数据库工具类（可通过数据源操作数据库）
 * Created by LiuXiaoLong at 2021年1月6日
 */
public class DBUtils {
	
	/**
	 * 测试链接
	 * @param dataSource 数据源
	 * @return
	 * @throws SQLException 
	 */
	public static boolean testConnection(DataSource dataSource) throws SQLException {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			return conn != null;
		}finally {
			JdbcUtils.close(conn);
		}
	}
	
	/**
	 * 根据Query SQL获取查询的字段
	 * @param dataSource
	 * @param sql
	 * @param parameterMap
	 * @return
	 */
	public static List<String> getColumnByQuerySql(DataSource dataSource,String sql,Map<String,Object> parameterMap) throws SQLException{
		Connection conn = null;
		List<String> columns = Lists.newArrayList();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			//加 1= 0 假查询条件可加快取字段名速度，避免因SQL查询速度过慢，导致加载列名效率也下降
			SQL _sql = SQLUtil.resolveSql(sql);
			String selectSql = _sql.getSelectSql();
			List<String> parameterNames = _sql.getParameterNames();
			
			if (selectSql.lastIndexOf("where") != -1) {
				StringBuilder sb = new StringBuilder(selectSql);
				sb.insert(sb.lastIndexOf("where")+5, " 1=0 and ");
				selectSql=sb.toString();
			}else {
				selectSql+=" where 1=0";
			}
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(selectSql);
			if (CollectionUtil.isNotEmpty(parameterNames)) {
				for (int i = 0; i < parameterNames.size(); i++) {
					String parameterName = parameterNames.get(i);
					Object val = parameterMap.get(parameterName);
					stmt.setObject(i+1, val == null ? "" : val);
				}
			}
			rs = stmt.executeQuery();
			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();
			for (int i = 1; i <= columnCount; i++) {
				columns.add(metaData.getColumnName(i));
			}
		} finally {
			JdbcUtils.close(conn);
			JdbcUtils.close(stmt);
			JdbcUtils.close(rs);
		}
		return columns;
	}
	

	/**
	 * 根据Query SQL获取数据
	 * @param dataSource
	 * @param sql
	 * @return
	 * @throws SQLException 
	 */
	public static List<Map<String, Object>> select(DataSource dataSource,String sql) throws SQLException{
		return select(dataSource, sql,Maps.newHashMap());
	}
	
	/**
	 * 根据Query SQL获取数据
	 * @param dataSource
	 * @param sql
	 * @param parameterMap
	 * @return
	 * @throws SQLException 
	 */
	public static List<Map<String, Object>> select(DataSource dataSource,String sql,Map<String,Object> parameterMap) throws SQLException{
		SQL _sql = SQLUtil.resolveSql(sql);
		List<String> parameterNames = _sql.getParameterNames();
		List<Object> parameters = packagingParam(parameterNames, parameterMap);
		return JdbcUtils.executeQuery(dataSource, _sql.getSelectSql(),parameters);
	}
	
	
	/**
	 * 查询当前数据库连接表名
	 * @param dataSource
	 * @return
	 * @throws SQLException 
	 */
	public static List<String> getTableNames(DataSource dataSource) throws SQLException{
		String dbType = getDbType(dataSource);
		DatabaseTool tool = DatabaseToolFactory.getTool(dbType);
		return tool.getTableNames(dataSource);
	}
	
	/**
	 * 判断该数据源中是否存在指定某表
	 * @param dataSource
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public static boolean existsTableName(DataSource dataSource,String tableName) throws SQLException {
		String dbType = getDbType(dataSource);
		DatabaseTool tool = DatabaseToolFactory.getTool(dbType);
		return tool.existsTableName(dataSource,tableName);
	}
	
	/**
	 * 根据表名查询列名
	 * @param dataSource
	 * @param tableName
	 * @return
	 * @throws SQLException 
	 */
	public static List<String> getColumnNames(DataSource dataSource,String tableName) throws SQLException{
		String dbType = getDbType(dataSource);
		DatabaseTool tool = DatabaseToolFactory.getTool(dbType);
		return tool.getColumnNames(dataSource,tableName);
	}
	
	/**
	 * 判断该数据源中指定表中是否存在某列
	 * @param dataSource
	 * @param tableName
	 * @param columnName
	 * @return
	 * @throws SQLException
	 */
	public static boolean existsColumnName(DataSource dataSource,String tableName,String columnName) throws SQLException {
		String dbType = getDbType(dataSource);
		DatabaseTool tool = DatabaseToolFactory.getTool(dbType);
		return tool.existsColumnName(dataSource,tableName,columnName);
	}

	/**
	 * 查询表数据，可指定查询列
	 * @param dataSource 数据源
	 * @param tableName 表名
	 * @param columns 列名集合
	 * @return
	 */
	public static List<Map<String, Object>> getTableDatas(DataSource dataSource, String tableName,String...columns) throws SQLException {
		return getTableDatas(dataSource, tableName, Arrays.asList(columns));
	}


	/**
	 * 查询表数据，可指定查询列
	 * @param dataSource 数据源
	 * @param tableName 表名
	 * @param columns 列名集合
	 * @return
	 */
	public static List<Map<String, Object>> getTableDatas(DataSource dataSource, String tableName,List<String> columns) throws SQLException {
		String sql = "select "+(CollectionUtil.isEmpty(columns) ? "*" : String.join(",", columns))+" from "+tableName;
		return JdbcUtils.executeQuery(dataSource, sql);
	}
	/**
	 * 分页查询
	 * @param dataSource
	 * @param pageRequest
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public static Page<Map<String, Object>> retrievePage(DataSource dataSource,
			Page<Map<String,Object>> page,String sql) throws SQLException{
		return retrievePage(dataSource,page,sql,Maps.newHashMap());
	}
	
	
	/**
	 * 分页查询
	 * @param dataSource
	 * @param pageRequest
	 * @param sql
	 * @param parameterMap
	 * @return
	 * @throws SQLException
	 */
	public static Page<Map<String, Object>> retrievePage(DataSource dataSource,
			Page<Map<String,Object>> page,String sql,Map<String,Object> parameterMap) throws SQLException{
		String dbType = getDbType(dataSource);
		DatabaseTool dbt = DatabaseToolFactory.getTool(dbType);
		SQL _sql = SQLUtil.resolveSql(sql);
		List<String> parameterNames = _sql.getParameterNames();
		List<Object> parameters = packagingParam(parameterNames, parameterMap);
		return dbt.retrievePage(dataSource,page,_sql.getSelectSql(),parameters);
	}
	
	
	/**
	 * 封装参数，将Map格式的参数转换为List格式
	 * @param parameterNames 参数名
	 * @param parameterMap 
	 * @return
	 */
	private static List<Object> packagingParam(List<String> parameterNames,Map<String, Object> parameterMap){
		List<Object> parameters = Lists.newArrayList();
		for (String str : parameterNames) {
			parameters.add(parameterMap == null ? "" : parameterMap.containsKey(str) ? parameterMap.get(str) : "" );//填充参数
		}
		return parameters;
	} 

	
	
	/**
	 * 创建数据库表
	 * @param ds 数据源
	 * @param table 表对象
	 * @throws SQLException 
	 */
	public static void createTable(DataSource ds,Table table) throws SQLException {
		if (table == null || StrUtil.isEmpty(table.getName()) || table.getColumns() == null || table.getColumns().size() == 0) {
			return;
		}
		String tableName = table.getName();
		String pkName = table.getPkName();
		StringBuffer sql = new StringBuffer("create table "+tableName+"(");
		List<Column> columns = table.getColumns();
		for (Column column : columns) {
			String columnName = column.getName();
			Boolean isNullable = column.getIsNullable();
			String columnType = column.bulidColumnType();
			sql.append(columnName+" "+columnType+" "+(isNullable ? "," : " not null ,"));
		}
		sql.substring(0, sql.length() -1);
		if (StrUtil.isNotEmpty(pkName)) {
			sql.append("primary key("+pkName+")");
		}
		sql.append(")");
		JdbcUtils.execute(ds, sql.toString());
	}
	
	/**
	 * 删除数据库表
	 * @param ds
	 * @param tableName
	 * @throws SQLException
	 */
	public static void dropTable(DataSource ds,String tableName) throws SQLException {
		String sql = "drop table "+tableName;
		JdbcUtils.execute(ds, sql);
	}
	
	/**
	 * 添加列
	 * @param ds 数据源
	 * @param tableName 表名
	 * @param column 列信息
	 * @throws SQLException
	 */
	public static void addColumn(DataSource ds,String tableName,Column column) throws SQLException{
		DatabaseToolFactory.getTool(getDbType(ds)).addColumn(ds,tableName,column);
	}
	
	/**
	 * 删除列
	 * @param ds 数据源
	 * @param tableName 表名
	 * @param dropColumnName 列名
	 * @throws SQLException
	 */
	public static void dropColumn(DataSource ds,String tableName,String dropColumnName) throws SQLException{
		DatabaseToolFactory.getTool(getDbType(ds)).dropColumn(ds, tableName, dropColumnName);
	}
	
	/**
	 * 修改列
	 * @param ds 数据源
	 * @param tableName 表名
	 * @param column 列信息
	 * @throws SQLException
	 */
	public static void modifyColumn(DataSource ds,String tableName,Column column) throws SQLException{
		DatabaseToolFactory.getTool(getDbType(ds)).modifyColumn(ds, tableName, column);
	}
	
	
	/**
	 * 获取数据库类型
	 * @param ds 数据源
	 * @return
	 */
	public static String getDbType(DataSource ds) {
		try {
			String url = org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(ds, "getURL");
			return JdbcUtils.getDbType(url);
		} catch (MetaDataAccessException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	/**
	 * 判断是否为数据库保留字
	 * @param str
	 * @return
	 */
	public static boolean isDBKeyword(String str) {
		if (StrUtil.isEmpty(str)) {
			return true;
		}
		String keys="PERCENT,PLAN,PRECISION,PRIMARY,PRINT,PROC,PROCEDURE,PUBLIC,RAISERROR,READ,READTEXT,RECONFIGURE,REFERENCES,REPLICATION,RESTORE,RESTRICT,RETURN,REVOKE,RIGHT,ROLLBACK,ROWCOUNT,ROWGUIDCOL,RULE,SAVE,SCHEMA,SELECT,SESSION_USER,SET,SETUSER,SHUTDOWN,SOME,STATISTICS,SYSTEM_USER,TABLE,TEXTSIZE,THEN,TO,TOP,TRAN,TRANSACTION,TRIGGER,TRUNCATE,TSEQUAL,UNION,UNIQUE,UPDATE,UPDATETEXT,USE,USER,VALUES,VARYING,VIEW,WAITFOR,WHEN,WHERE,WHILE,WITH,WRITETEXT,EXCEPT,EXEC,EXECUTE,EXISTS,EXIT,FETCH,FILE,FILLFACTOR,FOR,FOREIGN,FREETEXT,FREETEXTTABLE,FROM,FULL,FUNCTION,GOTO,GRANT,GROUP,HAVING,HOLDLOCK,IDENTITY,IDENTITY_INSERT,IDENTITYCOL,IF,IN,INDEX,INNER,INSERT,INTERSECT,INTO,IS,JOIN,KEY,KILL,LEFT,LIKE,LINENO,LOAD,NATIONAL,NOCHECK,NONCLUSTERED,NOT,NULL,NULLIF,OF,OFF,OFFSETS,ON,OPEN,OPENDATASOURCE,OPENQUERY,OPENROWSET,OPENXML,OPTION,OR,ORDER,OUTER,OVER,ADD,ALL,ALTER,AND,ANY,AS,ASC,AUTHORIZATION,BACKUP,BEGIN,BETWEEN,BREAK,BROWSE,BULK,BY,CASCADE,CASE,CHECK,CHECKPOINT,CLOSE,CLUSTERED,COALESCE,COLLATE,COLUMN,COMMIT,COMPUTE,CONSTRAINT,CONTAINS,CONTAINSTABLE,CONTINUE,CONVERT,CREATE,CROSS,CURRENT,CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP,CURRENT_USER,CURSOR,DATABASE,DBCC,DEALLOCATE,DECLARE,DEFAULT,DELETE,DENY,DESC,DISK,DISTINCT,DISTRIBUTED,DOUBLE,DROP,DUMMY,DUMP,ELSE,END,ERRLVL,ESCAPE,";
		keys+="ACCESS,ADD,ALL,ALTER,AND,ANY,AS,ASC,AUDIT,BETWEEN,BY,CHAR,"; 
		keys+="CHECK,CLUSTER,COLUMN,COMMENT,COMPRESS,CONNECT,CREATE,CURRENT,";
		keys+="DATE,DECIMAL,DEFAULT,DELETE,DESC,DISTINCT,DROP,ELSE,EXCLUSIVE,";
		keys+="EXISTS,FILE,FLOAT,FOR,FROM,GRANT,GROUP,HAVING,IDENTIFIED,";
		keys+="IMMEDIATE,IN,INCREMENT,INDEX,INITIAL,INSERT,INTEGER,INTERSECT,";
		keys+="INTO,IS,LEVEL,LIKE,LOCK,LONG,MAXEXTENTS,MINUS,MLSLABEL,MODE,";
		keys+="MODIFY,NOAUDIT,NOCOMPRESS,NOT,NOWAIT,NULL,NUMBER,OF,OFFLINE,ON,";
		keys+="ONLINE,OPTION,OR,ORDER,PCTFREE,PRIOR,PRIVILEGES,PUBLIC,RAW,";
		keys+="RENAME,RESOURCE,REVOKE,ROW,ROWID,ROWNUM,ROWS,SELECT,SESSION,";
		keys+="SET,SHARE,SIZE,SMALLINT,START,SUCCESSFUL,SYNONYM,SYSDATE,TABLE,";
		keys+="THEN,TO,TRIGGER,UID,UNION,UNIQUE,UPDATE,USER,VALIDATE,VALUES,";
		keys+="VARCHAR,VARCHAR2,VIEW,WHENEVER,WHERE,WITH";
		List<String> keyList = Arrays.asList(keys.split(","));
		return keyList.contains(str.toUpperCase());
	}
}
